package com.hnkywl.jip.dao;

import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * Created with IntelliJ IDEA.
 * User: jerry
 * Date: 13-2-25
 * Time: 下午8:36
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class InvoiceVerifyDao extends SimpleHibernateTemplate {
    /**
     * @param type       收款/付款
     * @param startTime  开始时间
     * @param endTime    结束时间
     * @param customerId 客户ID
     * @param companyId  公司ID
     * @param operator   操作人
     * @return
     */
    public Page<Object[]> findInvoiceVerify(Integer type, Date startTime, Date endTime, Integer invoiceType,
                                            Integer customerId, Integer companyId,
                                            Integer status, String voucherId, String operator, Integer limit, Integer start) {
        // sql查询字段
        String sqlColumn = "select a.compid," +
                "compname=(dbo.gf_compid_shortname(a.compid)), " +
                "a.kpid," +
                "kpno=dbo.gf_xxid_view(14,a.kpid)," +
                "c.regcustid,c.custid,dt=(case when a.status=1 then convert(varchar(10),a.appdt,102) else convert(varchar(10),a.dt,102) end), " +
                "typename=(case a.type when 1 then '进项' when 2 then '销售' else '物流' end), " +
                "custname=(dbo.gf_custid_name(c.custid)), " +
                "taxid=(select kptaxno from t_crm_regcust where regcustid=c.regcustid)," +
                "c.regcustid,kpje=(case when type in (1,2) then je else yfje end), a.dsdfje,neroje=0," +
                "invtype=(case a.invtype when 1 then '增票' when 2 then '普票' when 3 then '物流' when 9 then '另开票' else '其他' end), " +
                "invno1=(case when type in (1,2) then invno1 else invno2 end), " +
                "yfje,  invno2, " +
                "zdr=(select name from t_crm_man where manid=a.zdr), " +
                "chkdt=(case when a.status=2 then convert(varchar(10),a.dt,102) else null end), " +
                "kpr=(select name from t_crm_man where manid=a.kpr and a.status=2), " +
                "a.type, " +
                "a.jiuqi_voucher_num,isins=a.u8insflag,isdel=(case when a.u8insflag=3 and a.u8delflag=1 then 1 else 0 end)," +
                "isignseq=isnull(isignseq,0),iperiod=isnull(iperiod,0),isreset=(case when a.u8insflag=2 or a.u8delflag=2 then 1 else 0 end)," +
                "iswholetz=isnull(a.iswholetz,0),a.u8cnt, " +
                "operation=(case when :status in (1,4,5,9) then 'add' when :status=7 then 'delete' when  :status in (6,8) then 'reset' else 'none' end) " +
                "from t_cw_kp a inner join t_sys_comp b on a.compid=b.compid " +
                "left join t_crm_man d on a.kpr=d.manid,t_cw_cust c ";

        // 查询记录数
        String sqlCount = "select count(a.compid) " +
                "from t_cw_kp a inner join t_sys_comp b on a.compid=b.compid " +
                "left join t_crm_man d on a.kpr=d.manid,t_cw_cust c ";

        //查询条件
        String sqlCondition =
                "where  a.dt between :startTime and :endTime " +
                        (customerId != null ? "and a.custid=:customerId " : "") +
                        (type != null && type != 0 ? " and a.type=:type " : "") +
                        (companyId != null && companyId != 0 ? "and a.compid=:companyId " : "") +
                        " and a.status=2 " +
                        (invoiceType != null && invoiceType != 0 ? "and  a.invtype=:invoiceType " : "") +
                        (voucherId != null && !voucherId.isEmpty() ? "and a.jiuqi_voucher_num=:voucherId " : "") +
                        "and (isnull(a.iswholetz,0)=0 or isnull(a.istzddr,0)=1) " +
                        (operator != null && !operator.isEmpty() ? " and d.name like :operator " : "") +
                        "and isnull(a.hbid,0)=0 " +
                        "and a.custid=c.custid " +
                        " and ((:status in (1,2,3) and a.u8insflag=:status and isnull(a.jiuqi_insert_flag,1)=1) " +
                        " or (:status=4 and a.u8delflag=2) " +
                        " or (:status=5 and a.u8delflag=3)" +
                        " or (:status in(6,7) and a.jiuqi_insert_flag=:status-4)" +
                        " or (:status=8 and a.jiuqi_delete_flag=2)" +
                        " or (:status=9 and a.jiuqi_delete_flag=3)) ";
        String orderCondition = "order by a.status,(case when a.status=2 then a.dt else a.appdt end) desc";
        //获取总记录数
        SQLQuery query = createSQLQuery(sqlCount + sqlCondition);
        if (type != null && type != 0)
            query.setInteger("type", type);
        query.setDate("startTime", startTime);
        query.setDate("endTime", endTime);
        if (customerId != null)
            query.setInteger("customerId", customerId);
        if (invoiceType != null && invoiceType != 0)
            query.setInteger("invoiceType", invoiceType);
        if (status != null)
            query.setInteger("status", status);
        if (companyId != null && companyId != 0)
            query.setInteger("companyId", companyId);
        if (voucherId != null && !voucherId.isEmpty())
            query.setString("voucherId", voucherId);
        if (operator != null && !operator.isEmpty())
            query.setString("operator", "%" + operator + "%");
        Integer total = (Integer) query.uniqueResult();
        List<Object[]> result = null;

        if (total > 0) {
            //获取列表数据
            query = createSQLQuery(sqlColumn + sqlCondition + orderCondition);
            if (type != null && type != 0)
                query.setInteger("type", type);
            query.setDate("startTime", startTime);
            query.setDate("endTime", endTime);
            if (customerId != null)
                query.setInteger("customerId", customerId);
            if (invoiceType != null && invoiceType != 0)
                query.setInteger("invoiceType", invoiceType);
            if (status != null)
                query.setInteger("status", status);
            if (companyId != null && companyId != 0)
                query.setInteger("companyId", companyId);
            if (voucherId != null && !voucherId.isEmpty())
                query.setString("voucherId", voucherId);
            if (operator != null && !operator.isEmpty())
                query.setString("operator", "%" + operator + "%");
            result = query.setFirstResult(start).setMaxResults(limit).list();
        }
        //返回值
        Page<Object[]> page = new Page<Object[]>();
        page.setTotalCount(total);
        if (result != null && !result.isEmpty())
            page.setResult(result);
        return page;
    }


    /**
     * 发票凭证主表信息
     *
     * @param id 发票ID
     */
    public List getVoucherMaster(Integer id) {
        String sql = "select dbo.gf_cw_sys_csign(dbo.gf_sys_cwcompid_by_compid(k.compid)," +
                "dbo.gf_cw_sys_isignseq(k.compid,2)),c.company_name,c.company_code " +
                "from t_jiuqi_company c,t_cw_kp k where c.company_id=k.compid and k.kpid=?";
        Object[] result = (Object[]) createSQLQuery(sql, id).uniqueResult();
        if (result != null)
            return Arrays.asList(result);
        return null;
    }


    public List getVoucher(Integer id) {
        String sql = "{call sp_jiuqi_voucher_invoice(?,?,?)}";
        List<Object[]> list = createSQLQuery(sql, id, null, null).list();
        return list;
    }

    public void merge(String hbkpids,Integer userId) throws Exception {
        Connection conn = this.getSession().connection();
        ResultSet rs =null;
        CallableStatement call = null;
        try {
            call = conn.prepareCall("{call sp_cw_kp_hb(?,?,?,?,?)}");
            call.setInt(1,1);
            call.setInt(2, userId);
            call.setInt(3, 0);
            call.setString(4, hbkpids);
            call.registerOutParameter(5, Types.VARCHAR);
            if(!call.execute()){//执行
                String output = call.getString(1);//返回值
                throw new Exception(output);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public List getRevokeInfo(Integer id) {
        String sql = "select k.jiuqi_uuid,k.jiuqi_voucher_id,c.company_code from t_cw_kp k,t_jiuqi_company c where k.compid=c.company_id and k.kpid=?";
        Object[] result = (Object[]) createSQLQuery(sql, id).uniqueResult();
        if (result != null)
            return Arrays.asList(result);
        return null;
    }
}
